Performing INSERT, SELECT, UPDATE, and DELETE Operations in SQL

Course: Python Training

Topic: Performing INSERT, SELECT, UPDATE, and DELETE Operations

Course Link: Python Training Course at JustAcademy

Demo Registration: Register for a Course Demo

JustAcademy Homepage: Visit JustAcademy

1. Introduction to SQL Data Operations

SQL (Structured Query Language) is used to create, retrieve, modify, and manage data in relational databases. Four of the most important SQL operations are INSERT, SELECT, UPDATE, and DELETE. Together, these operations allow applications to perform the basic activities required to manage stored information.

For example, a student management application needs to add new student records, display existing students, update student details, and remove records when appropriate. These operations can be performed using SQL statements and connected to a Python application through a database driver.

The four operations are often introduced as CRUD operations:

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the purpose of INSERT, SELECT, UPDATE, and DELETE statements.
• Insert single and multiple records into a database table.
• Retrieve all columns or selected columns from a table.
• Filter records using WHERE and comparison operators.
• Sort query results using ORDER BY.
• Update specific records safely.
• Delete selected records without unintentionally removing unrelated data.
• Understand the importance of primary keys and constraints during data operations.
• Use parameterized queries to protect Python applications from SQL injection.
• Connect Python to SQLite and implement basic CRUD functionality.
• Handle database errors and manage transactions appropriately.

3. Preparing a Sample Database Table

Before performing data operations, create a table to store records. In this tutorial, the examples use a Students table containing student IDs, names, ages, courses, and email addresses.

Creating the Table

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100) NOT NULL,
    Age INT,
    Course VARCHAR(100),
    Email VARCHAR(150) UNIQUE
);

Explanation of the table structure:

• StudentID uniquely identifies each student.
• StudentName stores the student's name and cannot be NULL.
• Age stores the student's age.
• Course stores the course name.
• Email stores the student's email address and must be unique when a non-NULL value is supplied.

The exact data types and syntax can vary slightly between database systems. The examples use broadly familiar SQL syntax and can be adapted for MySQL, PostgreSQL, SQLite, or another relational database.

4. INSERT Operation: Adding New Records

The INSERT statement adds new records to an existing table. It is commonly used when a user registers for an account, a product is added to an inventory, or a new employee joins an organization.

4.1 Basic INSERT Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

INSERT INTO identifies the target table. The column list specifies which columns receive the supplied values, and VALUES provides the data for the new row.

4.2 Inserting a Single Record

INSERT INTO Students
    (StudentID, StudentName, Age, Course, Email)
VALUES
    (101, 'Aarav Sharma', 22, 'Python',
     '[email protected]');

This statement adds one student with ID 101. String values are enclosed in single quotes, while numeric values are written without quotes.

4.3 Inserting Multiple Records

Many database systems support inserting several rows in one statement. This can make bulk data entry more convenient.

INSERT INTO Students
    (StudentID, StudentName, Age, Course, Email)
VALUES
    (102, 'Diya Patel', 24, 'SQL',
     '[email protected]'),
    (103, 'Rohan Mehta', 21, 'Python',
     '[email protected]'),
    (104, 'Sara Khan', 23, 'Web Development',
     '[email protected]');

This statement inserts three records. Each row must provide values that correspond to the columns listed after the table name.

4.4 Inserting Data into Selected Columns

You do not always need to supply a value for every column. If omitted columns allow NULL or have suitable default values, they can be excluded from the column list.

INSERT INTO Students
    (StudentID, StudentName, Course)
VALUES
    (105, 'Kabir Shah', 'Database Fundamentals');

In this example, Age and Email are omitted. Their resulting values depend on the table definition, including defaults, nullability, and constraints.

4.5 Inserting Default Values

The DEFAULT keyword can request a column's defined default value when the database supports it in the relevant statement context.

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Status VARCHAR(20) DEFAULT 'Active'
);

INSERT INTO Courses (CourseID, CourseName, Status)
VALUES (1, 'Python Training', DEFAULT);

The inserted course receives the default status of Active in database systems that support this syntax.

4.6 Inserting Records Using INSERT ... SELECT

SQL can copy data from one table into another by using an INSERT statement with a SELECT query. The source and destination column counts and compatible data types must match.

INSERT INTO ArchivedStudents
    (StudentID, StudentName, Course)
SELECT
    StudentID, StudentName, Course
FROM Students
WHERE Course = 'Python';

This example copies matching Python student records into an archive table, assuming that the destination table exists and the data satisfies its constraints. It is useful for data migration and archiving tasks.

4.7 Common INSERT Errors

5. SELECT Operation: Retrieving Records

The SELECT statement retrieves data from one or more database tables. It is commonly used to display records in applications, generate reports, and analyze information.

5.1 Basic SELECT Syntax

SELECT column1, column2
FROM table_name;

5.2 Retrieving All Columns

SELECT *
FROM Students;

The asterisk (*) selects all columns from the table. It is convenient when exploring a table, but production queries often benefit from listing only the columns the application needs.

5.3 Retrieving Specific Columns

SELECT StudentName, Course
FROM Students;

This query returns only the student's name and course, rather than every column in the table.

5.4 Filtering Records with WHERE

The WHERE clause restricts query results to records that satisfy a condition.

SELECT *
FROM Students
WHERE Course = 'Python';

This query returns only students whose course value is Python.

5.5 Using Comparison Operators

5.6 Combining Conditions with AND and OR

AND requires both conditions to be true. OR requires at least one condition to be true.

SELECT *
FROM Students
WHERE Age >= 22 AND Course = 'Python';

SELECT *
FROM Students
WHERE Course = 'Python' OR Course = 'SQL';

Parentheses are helpful when combining several conditions because they make the intended logic easier to understand.

5.7 Filtering Values with IN and BETWEEN

The IN operator checks whether a value matches any value in a list.

SELECT *
FROM Students
WHERE Course IN ('Python', 'SQL');

The BETWEEN operator checks whether a value falls within an inclusive range.

SELECT *
FROM Students
WHERE Age BETWEEN 21 AND 24;

The query includes records with ages 21 and 24, as well as values between them.

5.8 Searching Text with LIKE

The LIKE operator matches text patterns. The percent sign (%) matches zero or more characters, while the underscore (_) matches one character in standard SQL pattern matching.

-- Names beginning with A
SELECT *
FROM Students
WHERE StudentName LIKE 'A%';

-- Names containing the text "ar"
SELECT *
FROM Students
WHERE StudentName LIKE '%ar%';

Case sensitivity depends on the database system and its collation settings.

5.9 Handling NULL Values

NULL represents missing or unknown information. Use IS NULL and IS NOT NULL to test whether a column contains NULL.

SELECT *
FROM Students
WHERE Email IS NULL;

SELECT *
FROM Students
WHERE Email IS NOT NULL;

Do not use Email = NULL to check for missing values because ordinary equality comparisons do not work with NULL in the same way they work with regular values.

5.10 Sorting with ORDER BY

The ORDER BY clause sorts the retrieved records.

-- Ascending order
SELECT StudentName, Age
FROM Students
ORDER BY Age ASC;

-- Descending order
SELECT StudentName, Age
FROM Students
ORDER BY Age DESC;

ASC sorts in ascending order, while DESC sorts in descending order. If multiple records share the same value, add a secondary sort column when a stable ordering is needed.

5.11 Limiting Query Results

Some database systems support LIMIT to restrict the number of returned rows.

SELECT StudentID, StudentName
FROM Students
ORDER BY StudentID
LIMIT 3;

This example returns up to three rows. The syntax for limiting results varies by database system; for example, SQL Server commonly uses TOP or OFFSET ... FETCH.

5.12 Using DISTINCT

The DISTINCT keyword removes duplicate combinations from the query result.

SELECT DISTINCT Course
FROM Students;

This query lists each distinct course represented in the student records.

6. UPDATE Operation: Modifying Existing Records

The UPDATE statement changes values in existing rows. It is useful when a student's course changes, an employee's salary is revised, or a product's price needs to be adjusted.

6.1 Basic UPDATE Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

The SET clause specifies the columns to modify. The WHERE clause identifies which records should be updated.

6.2 Updating a Single Record

UPDATE Students
SET Course = 'Advanced Python'
WHERE StudentID = 101;

This statement changes the course only for the student whose ID is 101.

6.3 Updating Multiple Columns

UPDATE Students
SET StudentName = 'Aarav Singh',
    Age = 23,
    Course = 'Python Development'
WHERE StudentID = 101;

Multiple columns can be modified in a single UPDATE statement. The conditions determine which rows receive the new values.

6.4 Updating Multiple Records

UPDATE Students
SET Course = 'SQL Fundamentals'
WHERE Course = 'SQL';

This query changes the course for every student whose current course is SQL. Multiple rows may be updated if they match the condition.

6.5 Updating Values Using Expressions

An UPDATE statement can calculate a new value using the existing column value.

UPDATE Products
SET Price = Price * 1.10
WHERE Category = 'Books';

This example increases prices in the Books category by 10%, assuming the table has the specified columns. Before executing a bulk update, verify the affected rows and consider the effect of rounding and constraints.

6.6 Updating Records with Multiple Conditions

UPDATE Students
SET Course = 'Database Advanced'
WHERE Age >= 23
  AND Course = 'Database Fundamentals';

The update applies only to records satisfying both conditions.

6.7 Why WHERE Is Important in UPDATE

An UPDATE statement without a WHERE clause generally modifies every row in the table.

-- Dangerous: changes every student's course.
UPDATE Students
SET Course = 'Python';

To avoid unintended changes, preview the target records using a SELECT query before running the update.

SELECT StudentID, StudentName, Course
FROM Students
WHERE StudentID = 101;

Once the target records have been confirmed, execute the corresponding UPDATE statement.

7. DELETE Operation: Removing Records

The DELETE statement removes rows from a table. It is commonly used to remove test records, cancel obsolete entries, or delete records that are no longer required according to an application's data-retention rules.

7.1 Basic DELETE Syntax

DELETE FROM table_name
WHERE condition;

7.2 Deleting a Single Record

DELETE FROM Students
WHERE StudentID = 105;

This statement removes the student record with ID 105, provided the deletion is allowed by the database's constraints and relationships.

7.3 Deleting Records Based on a Condition

DELETE FROM Students
WHERE Age < 18;

This statement removes all rows that satisfy the age condition. Always verify that the condition matches the intended records before executing a DELETE statement.

7.4 Deleting Records with Multiple Conditions

DELETE FROM Students
WHERE Course = 'Temporary Course'
  AND StudentID = 200;

Both conditions must be true for a row to be deleted. Including a unique identifier can make the target more precise.

7.5 Deleting All Records

DELETE FROM Students;

This statement deletes every row in the table because it has no WHERE clause. The table structure remains in place. Do not run this statement unless removing all records is intentional and authorized.

7.6 DELETE vs TRUNCATE vs DROP

Transaction behavior, identity counter behavior, and foreign-key restrictions vary between database systems. TRUNCATE and DROP should not be treated as interchangeable with a filtered DELETE.

8. Comparing INSERT, SELECT, UPDATE, and DELETE

9. Performing CRUD Operations in Python Using SQLite

Python includes the sqlite3 module, which allows applications to create and work with SQLite databases without installing a separate database server. SQLite is suitable for learning SQL, prototypes, local applications, and some production use cases.

Python applications should use parameterized queries rather than combining user input directly into SQL strings. Parameterized queries help prevent SQL injection and correctly pass values to the database driver.

9.1 Connecting to an SQLite Database

import sqlite3

connection = sqlite3.connect("students.db")
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS Students (
        StudentID INTEGER PRIMARY KEY,
        StudentName TEXT NOT NULL,
        Age INTEGER,
        Course TEXT,
        Email TEXT UNIQUE
    )
""")

connection.commit()

The connection opens or creates a database file named students.db. The table is created only if it does not already exist.

9.2 INSERT Using Python

cursor.execute(
    """
    INSERT INTO Students
        (StudentName, Age, Course, Email)
    VALUES (?, ?, ?, ?)
    """,
    ("Aarav Sharma", 22, "Python", "[email protected]")
)

connection.commit()
print("Student inserted successfully.")

The question marks are SQLite parameter placeholders. The tuple supplies values in the same order as the placeholders. Because the primary key is defined as INTEGER PRIMARY KEY, SQLite can assign the identifier automatically when it is omitted from the INSERT statement.

9.3 SELECT Using Python

cursor.execute(
    "SELECT StudentID, StudentName, Course FROM Students"
)

rows = cursor.fetchall()

for row in rows:
    print(row)

fetchall() retrieves the remaining rows from the query result as a list. Each row is returned as a tuple by default in the standard SQLite interface.

9.4 SELECT with a Parameterized Filter

course_name = "Python"

cursor.execute(
    "SELECT StudentID, StudentName FROM Students WHERE Course = ?",
    (course_name,)
)

for row in cursor.fetchall():
    print(row)

The trailing comma in (course_name,) creates a one-item tuple. Passing parameters separately keeps the value separate from the SQL statement.

9.5 UPDATE Using Python

student_id = 1
new_course = "Advanced Python"

cursor.execute(
    "UPDATE Students SET Course = ? WHERE StudentID = ?",
    (new_course, student_id)
)

connection.commit()
print("Rows affected:", cursor.rowcount)

The code updates the course for the selected student and commits the change. The value of rowcount is provided by the database interface; consult the driver documentation when exact row-count behavior matters.

9.6 DELETE Using Python

student_id = 1

cursor.execute(
    "DELETE FROM Students WHERE StudentID = ?",
    (student_id,)
)

connection.commit()
print("Rows affected:", cursor.rowcount)

This code deletes the student whose ID matches the supplied value. In real applications, verify the user's authorization and the target record before deleting important data.

9.7 Closing the Connection

cursor.close()
connection.close()

Closing database resources when they are no longer needed helps prevent resource leaks. For larger applications, context managers and a clearly defined transaction strategy can simplify resource management.

10. Complete Python CRUD Example

The following example brings the four SQL operations together in one small program. It creates a table, inserts a student, retrieves records, updates a record, and deletes it.

import sqlite3

def main():
    connection = sqlite3.connect("crud_demo.db")

    try:
        cursor = connection.cursor()

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS Students (
                StudentID INTEGER PRIMARY KEY,
                StudentName TEXT NOT NULL,
                Age INTEGER,
                Course TEXT
            )
        """)

        # INSERT
        cursor.execute(
            """
            INSERT INTO Students (StudentName, Age, Course)
            VALUES (?, ?, ?)
            """,
            ("Diya Patel", 24, "SQL")
        )
        student_id = cursor.lastrowid
        connection.commit()
        print("Inserted student ID:", student_id)

        # SELECT
        cursor.execute(
            """
            SELECT StudentID, StudentName, Age, Course
            FROM Students
            WHERE StudentID = ?
            """,
            (student_id,)
        )
        print("Inserted record:", cursor.fetchone())

        # UPDATE
        cursor.execute(
            """
            UPDATE Students
            SET Course = ?
            WHERE StudentID = ?
            """,
            ("Advanced SQL", student_id)
        )
        connection.commit()
        print("Updated rows:", cursor.rowcount)

        cursor.execute(
            """
            SELECT StudentID, StudentName, Age, Course
            FROM Students
            WHERE StudentID = ?
            """,
            (student_id,)
        )
        print("Updated record:", cursor.fetchone())

        # DELETE
        cursor.execute(
            "DELETE FROM Students WHERE StudentID = ?",
            (student_id,)
        )
        connection.commit()
        print("Deleted rows:", cursor.rowcount)

    except sqlite3.Error as error:
        connection.rollback()
        print("Database error:", error)

    finally:
        connection.close()

if __name__ == "__main__":
    main()

Important: This is a learning example. Because the table persists in the database file, the program can be run multiple times. In a real application, consider how repeated inserts, unique constraints, transactions, logging, and user authorization should be handled.

11. Transactions and Safe Data Modification

A transaction groups related database operations into a unit of work. Transactions are important when multiple changes must succeed together. If an operation fails before the transaction is committed, the application can roll back the changes according to the database's transaction behavior.

Example: Transaction with SQLite

import sqlite3

connection = sqlite3.connect("students.db")

try:
    cursor = connection.cursor()

    cursor.execute(
        "UPDATE Students SET Course = ? WHERE StudentID = ?",
        ("Python Advanced", 1)
    )

    cursor.execute(
        "UPDATE Students SET Age = ? WHERE StudentID = ?",
        (25, 1)
    )

    connection.commit()
    print("Changes committed successfully.")

except sqlite3.Error as error:
    connection.rollback()
    print("Transaction failed:", error)

finally:
    connection.close()

The two updates are committed together in this example. If a database error occurs before the commit, the exception handler requests a rollback. For more complex operations, validate all required conditions and confirm that the expected records were affected before committing.

Safe Practices Before UPDATE or DELETE

• Run a SELECT query with the intended condition to inspect the target records.
• Use a primary key or another appropriately specific condition where possible.
• Check the affected-row count when supported and meaningful.
• Use transactions for related changes that must remain consistent.
• Ensure backups and recovery procedures are available for important data.
• Do not assume every database system handles transactions identically.

12. SQL Injection and Parameterized Queries

SQL injection occurs when untrusted input is combined with SQL command text in a way that changes the intended query. It can allow attackers to read or modify data beyond the intended scope of an operation.

Unsafe Example

# Do not use this pattern with untrusted input.
student_name = input("Enter student name: ")

query = (
    "SELECT * FROM Students "
    + "WHERE StudentName = '" + student_name + "'"
)

Building a SQL statement this way is unsafe because input may contain characters that alter the query's meaning.

Safe Example Using Parameters

student_name = input("Enter student name: ")

cursor.execute(
    "SELECT * FROM Students WHERE StudentName = ?",
    (student_name,)
)

In this version, the input is passed separately as a parameter. The database driver handles the value according to its parameter-binding rules. Always use the placeholder syntax supported by the specific database driver.

13. Best Practices for CRUD Operations

• Specify columns in INSERT statements: Avoid relying on the physical order of table columns.
• Use meaningful SELECT queries: Retrieve only the columns required by the application.
• Always check UPDATE and DELETE conditions: A missing or incorrect WHERE clause can affect too many records.
• Use primary keys: Unique identifiers make it easier to target individual records reliably.
• Use parameterized queries: Never concatenate untrusted user input directly into SQL commands.
• Validate inputs: Check required fields, expected formats, and business rules before executing operations.
• Respect constraints: Understand primary keys, foreign keys, unique constraints, and NOT NULL rules.
• Manage transactions: Commit successful changes and roll back failed operations when appropriate.
• Handle errors carefully: Catch relevant database exceptions and log useful diagnostic information without exposing sensitive data.
• Close resources: Release cursors and connections when they are no longer needed.
• Use least privilege: Database accounts should have only the permissions required for their work.
• Test with sample data: Verify both successful and unsuccessful cases before deploying database code.

14. Common Mistakes and Troubleshooting

15. Practical Project: Student Record Management System

Create a simple student management system using Python and SQLite. The project should demonstrate all four CRUD operations through a command-line interface or a basic application interface.

Project Requirements

• Create a database file and a Students table.
• Add a student using INSERT.
• Display all students using SELECT.
• Search for a student by ID using a parameterized query.
• Update the course or age of a selected student.
• Delete a student only after confirming the target record.
• Validate user input before executing database operations.
• Handle duplicate identifiers and invalid data gracefully.
• Use transactions where appropriate and close the database connection.

Suggested Menu

1. Add Student
2. View All Students
3. Search Student by ID
4. Update Student
5. Delete Student
6. Exit

Organize each menu action into a separate Python function. This makes the application easier to read, test, debug, and extend.

16. Exercises for Practice

Beginner-Level Exercises

1. Create an Employees table with employee ID, name, department, and salary.
2. Insert five employee records using INSERT statements.
3. Retrieve all employee records using SELECT.
4. Display only the names and departments of employees.
5. Retrieve employees whose salary exceeds a chosen amount.

Intermediate-Level Exercises

1. Update the department of a particular employee using the employee ID.
2. Increase the salary of employees in a specified department by 5%.
3. Delete a single test employee using a precise WHERE condition.
4. Use ORDER BY to list employees by salary from highest to lowest.
5. Write a query that selects employees whose names begin with a specified letter.

Python Integration Exercises

1. Connect Python to SQLite and create a table if it does not exist.
2. Write a function that inserts a new record using parameterized SQL.
3. Write a function that returns all records using SELECT.
4. Write a function that updates a record by its primary key.
5. Write a function that deletes a record after confirming the target ID.
6. Add error handling and transaction management to the CRUD functions.

17. Interview Questions and Answers

Q1. What are CRUD operations?

Answer: CRUD stands for Create, Read, Update, and Delete. These operations represent the basic activities used to manage records in a database. They are commonly implemented with INSERT, SELECT, UPDATE, and DELETE statements.

Q2. What is the purpose of the INSERT statement?

Answer: INSERT adds new records to a table. It can insert a single row or, in many database systems, multiple rows in one statement.

Q3. What is the purpose of SELECT?

Answer: SELECT retrieves information from one or more tables. It can return selected columns, filter rows, sort results, and perform calculations.

Q4. Why is the WHERE clause important in UPDATE?

Answer: WHERE specifies which rows should be modified. Without it, an UPDATE statement generally changes every row in the target table.

Q5. What happens when DELETE is used without WHERE?

Answer: All rows in the target table are deleted, although the table structure remains. The operation should only be performed when removing every row is intentional.

Q6. What is the difference between DELETE and DROP?

Answer: DELETE removes rows from a table, whereas DROP TABLE removes the table object and its stored data.

Q7. What is the purpose of ORDER BY?

Answer: ORDER BY sorts query results in ascending or descending order based on one or more expressions or columns.

Q8. What is a parameterized query?

Answer: A parameterized query passes data values separately from SQL command text. It improves safety and helps prevent SQL injection.

Q9. How does Python execute an SQL query using SQLite?

Answer: Python can use sqlite3.connect() to open a database, then use a connection or cursor's execute() method to run a query. Data changes are committed as required, and result rows can be retrieved using methods such as fetchone() and fetchall().

Q10. What is the purpose of commit()?

Answer: commit() confirms the current transaction's changes so they are saved according to the database's transaction behavior.

Q11. What is rollback()?

Answer: rollback() requests that uncommitted changes in the current transaction be undone.

Q12. What does cursor.rowcount indicate?

Answer: It provides information about the number of rows affected by or associated with an operation, depending on the driver and statement. Its exact behavior varies, especially for SELECT queries and certain bulk operations.

Q13. Can INSERT add data from another table?

Answer: Yes. An INSERT statement can use a SELECT query as its source, allowing rows returned by the query to be inserted into a destination table.

Q14. How can you safely delete a specific record?

Answer: Use a precise WHERE condition, preferably based on a unique identifier, pass any variable values as parameters, verify the target record, and apply transaction controls when appropriate.

Q15. Why should database connections be closed?

Answer: Closing connections releases resources and helps prevent resource leaks. Applications should manage connections and cursors according to the database driver's recommended practices.

18. Quick Revision Checklist

• Understand the purpose and syntax of INSERT, SELECT, UPDATE, and DELETE.
• Specify target columns when inserting data.
• Use WHERE to filter SELECT results and target UPDATE or DELETE operations.
• Use ORDER BY to sort query results.
• Understand NULL handling, data types, and table constraints.
• Verify target records before performing destructive operations.
• Use parameterized SQL queries in Python applications.
• Understand commit and rollback behavior.
• Handle database errors and close resources properly.
• Practice implementing CRUD operations in a small SQLite project.

19. Conclusion

INSERT, SELECT, UPDATE, and DELETE are the foundation of database-driven application development. INSERT adds records, SELECT retrieves information, UPDATE modifies existing records, and DELETE removes records that are no longer required.

Python developers frequently combine these SQL operations with SQLite or other relational database systems to build applications that persist information. Learning parameterized queries, WHERE conditions, constraints, transaction management, and error handling is essential for writing safe and reliable database code.

Continue learning through the JustAcademy Python Training Course, visit the JustAcademy homepage, or register for a course demo to explore training opportunities.